package com.py.credit.service.impl;

import com.py.credit.common.Page;
import com.py.credit.dao.LhjcHomePageDao;
import com.py.credit.entity.ChartNum;
import com.py.credit.entity.FeedbackInfo;
import com.py.credit.entity.GzInfo;
import com.py.credit.entity.MessInfo;
import com.py.credit.service.LhjcHomePageService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

@Service
public class LhjcHomePageServiceImpl implements LhjcHomePageService {

    @Autowired
    private LhjcHomePageDao lhjcHomePageDao;

    @Override
    public List<MessInfo> getAjbhtListForPage(String jclb, long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.getAjbhtListForPage(jclb, deptId, startTime, endTime, page);
    }

    @Override
    public List<MessInfo> getOtherAjbhtListForPage(String jclb, long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.getOtherAjbhtListForPage(jclb, deptId, startTime, endTime, page);
    }

    @Override
    public List<FeedbackInfo> getFeedbackListForPage(long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.getFeedbackListForPage(deptId, startTime, endTime, page);
    }

    @Override
    public List<FeedbackInfo> getOtherDeptFeedbackListForPage(long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.getOtherDeptFeedbackListForPage(deptId, startTime, endTime, page);
    }

    @Override
    public List<GzInfo> selectGzInfoListForPage(String jclb, long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.selectGzInfoListForPage(jclb, deptId, startTime, endTime, page);
    }

    @Override
    public List<GzInfo> selectOtherGzInfoListForPage(String jclb, long deptId, String startTime, String endTime, Page page) {
        return lhjcHomePageDao.selectOtherGzInfoListForPage(jclb, deptId, startTime, endTime, page);
    }

    @Override
    public List<ChartNum> selectFqNum(long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.selectFqNum(deptId, startTime, endTime);
    }

    @Override
    public List<ChartNum> selectCyNum(long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.selectCyNum(deptId, startTime, endTime);
    }

    @Override
    public List<ChartNum> selectXyNum(long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.selectXyNum(deptId, startTime, endTime);
    }

    @Override
    public Map<String, String> selectMaxTime() {
        return lhjcHomePageDao.selectMaxTime();
    }

    @Override
    public Map<String, String> selectMinTime() {
        return lhjcHomePageDao.selectMinTime();
    }

    @Override
    public void excelExport(HttpServletResponse response, long deptId, String startTime, String endTime) throws IOException {
        queryTableExecle(response, deptId, startTime, endTime);
    }

    @Override
    public int getAjbhtListForTotalCount(String jclb, long cfbm, String startTime, String endTime) {
        return lhjcHomePageDao.getAjbhtListForTotalCount(jclb, cfbm, startTime, endTime);
    }

    @Override
    public int getOtherAjbhtListForTotalCount(String jclb, long cfbm, String startTime, String endTime) {
        return lhjcHomePageDao.getOtherAjbhtListForTotalCount(jclb, cfbm, startTime, endTime);
    }

    @Override
    public int getFeedbackListForTotalCount(long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.getFeedbackListForTotalCount(deptId, startTime, endTime);
    }

    @Override
    public int getOtherDeptFeedbackListForTotalCount(long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.getOtherDeptFeedbackListForTotalCount(deptId, startTime, endTime);
    }

    @Override
    public int selectGzInfoListForTotalCount(String jclb, long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.selectGzInfoListForTotalCount(jclb, deptId, startTime, endTime);
    }

    @Override
    public int selectOtherGzInfoListForTotalCount(String jclb, long deptId, String startTime, String endTime) {
        return lhjcHomePageDao.selectOtherGzInfoListForTotalCount(jclb, deptId, startTime, endTime);
    }

    private void queryTableExecle(HttpServletResponse response, long deptId, String startTime, String endTime) throws IOException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        // 生成提示信息
        response.setContentType("application/vnd.ms-excel");
        HSSFWorkbook workbook = null;
        OutputStream fOut = null;
        String codedFileName = "联合奖惩统计";
        // 进行转码，使其支持中文文件名
        codedFileName = URLEncoder.encode(codedFileName, "UTF-8");
        response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");
        // 产生工作簿对象
        workbook = new HSSFWorkbook();
        // 产生工作表对象
        HSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "本部门发起联合奖惩事件");
        HSSFRow row = null;
        HSSFCell cell = null;

        String[] strBT = {"案件编码", "主体名称", "规则名称", "发起时间", "期限", "数据状态"};
        List<MessInfo> ajbhList = lhjcHomePageDao.getAjbhList("", deptId, startTime, endTime);

        //创建标题行
        row = sheet.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        HSSFSheet sheet2 = workbook.createSheet();
        workbook.setSheetName(1, "本部门发起联合激励事件");
        row = null;
        cell = null;
        ajbhList = lhjcHomePageDao.getAjbhList("J", deptId, startTime, endTime);

        //创建标题行
        row = sheet2.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet2.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        HSSFSheet sheet3 = workbook.createSheet();
        workbook.setSheetName(2, "本部门发起联合惩戒事件");
        row = null;
        cell = null;
        ajbhList = lhjcHomePageDao.getAjbhList("C", deptId, startTime, endTime);

        //创建标题行
        row = sheet3.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet3.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        String[] feedbackArr = {"部门", "反馈", "案件编码", "主体名称", "发起时间", "反馈时间"};
        HSSFSheet sheet4 = workbook.createSheet();
        workbook.setSheetName(3, "各部门反馈");
        row = null;
        cell = null;
        List<FeedbackInfo> feedbackList = lhjcHomePageDao.getOtherDeptFeedbackList(deptId, startTime, endTime);

        //创建标题行
        row = sheet4.createRow(0);
        for (int i = 0; i < feedbackArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackArr[i]);
        }

        for (int i = 0; i < feedbackList.size(); i++) {
            row = sheet4.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getDeptName());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getFeedbackMess());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getAjbh());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getZtmc());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(format.format(feedbackList.get(i).getAddTime()));

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(format.format(feedbackList.get(i).getFeedbackTime()));
        }
        feedbackList = null;

        String[] gzInfoArr = {"联合奖惩规则名称", "编码", "联合奖惩制度全称", "触发部门", "规则状态"};
        HSSFSheet sheet5 = workbook.createSheet();
        workbook.setSheetName(4, "本部门发起联合奖惩规则");
        row = null;
        cell = null;
        List<GzInfo> gzInfoList = lhjcHomePageDao.getGzInfoList("", deptId, startTime, endTime);

        //创建标题行
        row = sheet5.createRow(0);
        for (int i = 0; i < gzInfoArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoArr[i]);
        }

        for (int i = 0; i < gzInfoList.size(); i++) {
            row = sheet5.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getName());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getBm());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getDetailName());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getCfbm());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (gzInfoList.get(i).getGzzt().equals("N")) {
                cell.setCellValue("未发布");
            } else {
                cell.setCellValue("已发布");
            }

        }
        gzInfoList = null;

        HSSFSheet sheet6 = workbook.createSheet();
        workbook.setSheetName(5, "本部门参与联合奖惩事件");
        row = null;
        cell = null;
        ajbhList = lhjcHomePageDao.getOhterAjbhList("", deptId, startTime, endTime);

        //创建标题行
        row = sheet6.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet6.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        HSSFSheet sheet7 = workbook.createSheet();
        workbook.setSheetName(6, "本部门参与联合激励事件");
        row = null;
        cell = null;
        ajbhList = lhjcHomePageDao.getOhterAjbhList("J", deptId, startTime, endTime);

        //创建标题行
        row = sheet7.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet7.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        HSSFSheet sheet8 = workbook.createSheet();
        workbook.setSheetName(7, "本部门参与联合惩戒事件");
        row = null;
        cell = null;
        ajbhList = lhjcHomePageDao.getOhterAjbhList("C", deptId, startTime, endTime);

        //创建标题行
        row = sheet8.createRow(0);
        for (int i = 0; i < strBT.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(strBT[i]);
        }

        for (int i = 0; i < ajbhList.size(); i++) {
            row = sheet8.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getAjbh());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getZtmc());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getGzmc());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getSxsj());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(ajbhList.get(i).getRepairTime());

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (ajbhList.get(i).getStatus().equals("J")) {
                cell.setCellValue("进行中");
            } else if (ajbhList.get(i).getStatus().equals("G")) {
                cell.setCellValue("已失效");
            } else {
                cell.setCellValue("已移出");
            }
        }
        ajbhList = null;

        HSSFSheet sheet9 = workbook.createSheet();
        workbook.setSheetName(8, "处理反馈");
        row = null;
        cell = null;
        feedbackList = lhjcHomePageDao.getFeedbackList(deptId, startTime, endTime);

        //创建标题行
        row = sheet9.createRow(0);
        for (int i = 0; i < feedbackArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackArr[i]);
        }

        for (int i = 0; i < feedbackList.size(); i++) {
            row = sheet9.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getDeptName());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getFeedbackMess());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getAjbh());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(feedbackList.get(i).getZtmc());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(format.format(feedbackList.get(i).getAddTime()));

            cell = row.createCell(5);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(format.format(feedbackList.get(i).getFeedbackTime()));
        }
        feedbackList = null;

        HSSFSheet sheet10 = workbook.createSheet();
        workbook.setSheetName(9, "本部门参与联合奖惩规则");
        row = null;
        cell = null;
        gzInfoList = lhjcHomePageDao.getOtherGzInfoList("", deptId, startTime, endTime);

        //创建标题行
        row = sheet10.createRow(0);
        for (int i = 0; i < gzInfoArr.length; i++) {
            cell = row.createCell(i);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoArr[i]);
        }

        for (int i = 0; i < gzInfoList.size(); i++) {
            row = sheet10.createRow(i + 1);// 创建一行

            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getName());

            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getBm());

            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getDetailName());

            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue(gzInfoList.get(i).getCfbm());

            cell = row.createCell(4);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            if (gzInfoList.get(i).getGzzt().equals("N")) {
                cell.setCellValue("未发布");
            } else {
                cell.setCellValue("已发布");
            }

        }
        gzInfoList = null;

        fOut = response.getOutputStream();
        workbook.write(fOut);
        workbook.close();
        fOut.flush();
        fOut.close();
    }
}
